# 数据口径与 SQL 核验

这个项目首先解决的不是“怎样把中文翻译成 SQL”，而是“SQL 查出来的数字究竟代表什么”。数据库能成功执行一条查询，并不能说明它回答了用户的问题。

## 六张表与数据粒度

| 表 | 一行代表什么 | 在收入分析中的作用 |
| --- | --- | --- |
| orders | 一笔订单 | 支付金额、支付时间、地区快照、渠道 |
| order_items | 一笔订单中的一个商品明细行 | 未来商品分析；当前不用于累计订单金额 |
| products | 一个商品 | 商品与分类维度 |
| users | 一个用户 | 当前资料；当前地区不能替代历史订单地区 |
| channels | 一个渠道 | 渠道枚举，保证零值渠道也出现 |
| refunds | 一次退款记录 | 成功金额及实际退款时间 |

关系为：一个用户有多笔订单，一个渠道有多笔订单；一笔订单可有多个商品明细，也可有多次退款。商品通过订单明细关联订单。先说清“一行是什么”，才能判断哪些连接会把钱重复加起来。

模拟数据覆盖 2026 年 6 月至 8 月，固定随机种子为 20260916。数据专门包含跨月支付、早期订单在后月退款、一单多退、失败退款和取消订单。它们是检验口径的陷阱样本，而不是随便凑出的演示行。

## 净收入的口径

本项目定义：某月某地区净收入 = 该月该地区成功支付订单的实付金额 − 该月成功发生的退款金额。这是项目经营分析口径，不应自动等同于企业会计准则下的收入确认。

| 项目 | 本项目规则 | 直观例子 |
| --- | --- | --- |
| 订单归属月份 | paid_at，且 status=paid | 7 月 31 日创建、8 月 1 日支付，算八月 |
| 退款归属月份 | refunded_at，且 status=success | 七月付款、八月退款，退款冲减八月 |
| 地区 | orders.shipping_region | 用户后来迁居，历史订单地区不变 |
| 退款渠道 | 原订单 channel_id | 退款不会变成独立获客渠道 |
| 金额 | 原始单位为整数分 | 19.99 元存为 1999 |
| 时间范围 | 左闭右开 | 八月为 [8 月 1 日，9 月 1 日) |

为什么不能用 `created_at`？它回答的是“何时下单”，不是“何时支付”。为什么不能用用户当前地址？那会使过去月份的收入随用户改地址而变动。为什么退款查询不能要求原订单也在本月支付？因为旧订单本月退款同样会影响本月净收入。

时间字符串在当前数据中采用一致、可按字典序比较的 ISO 格式，并约定上海时区。代码没有替任意来源的带时区时间做统一转换；接入真实数据时必须先规范时间。

## 多表连接怎样把收入算大

假设订单 A 实付 100 元，有两行商品，分别 60 元和 40 元；又有两次成功退款，分别 10 元和 20 元。若直接执行“订单 JOIN 明细 JOIN 退款”，中间结果有四行：

| 订单 | 商品行 | 退款行 | 订单实付 |
| --- | --- | --- | --- |
| A | 商品 1 | 退款 1 | 100 |
| A | 商品 1 | 退款 2 | 100 |
| A | 商品 2 | 退款 1 | 100 |
| A | 商品 2 | 退款 2 | 100 |

对订单实付求和得到 400 元，对退款求和得到 60 元，而正确值是 100 元和 30 元。`COUNT(DISTINCT order_id)` 只能救订单数，救不了金额。`SUM(DISTINCT paid_cents)` 也不对：两笔都付 100 元的不同订单会被错误合成一笔。

正确原则是先在各自的粒度上汇总，再连接汇总结果。本项目把支付与退款各自聚合到“期间 × 渠道”，此时每边同一键最多一行，再关联就不会因一单多明细、多退款而放大金额。

## 标准 SQL 的四段逻辑

`commerce/query.py` 中的 `STANDARD_SQL` 是完整基准查询。阅读时不要被 CTE 语法吓住：WITH 只是给中间结果取名字。

第一段 `periods` 生成两行时间窗口，分别叫 previous 和 current。第二段 `payments` 按支付时间选择 paid 订单，用收货地区筛选，按期间和渠道统计订单数及实付。第三段 `successful_refunds` 按退款时间选择 success 记录，连接原订单只为取得地区和渠道，不限制原订单支付月份。

第四段把两期和所有渠道做笛卡尔积，得到完整的六个位置，再 LEFT JOIN 两张聚合结果。没有订单或退款的位置用 COALESCE 填零。这一步确保“零订单”不会和“丢了一行”混淆。

```sql
-- 核心结构示意，完整可执行版本见 query.py
WITH periods AS (...),
payments AS (... GROUP BY period, channel_id),
successful_refunds AS (... GROUP BY period, channel_id)
SELECT period, channel, order_count, paid_cents, refund_cents
FROM periods CROSS JOIN channels
LEFT JOIN payments ON 期间与渠道都相同
LEFT JOIN successful_refunds ON 期间与渠道都相同;
```

模型可以写不同形式的 SQL，但输出必须遵守同样的五列契约，结果必须与明细核验一致。系统并不是只接受和标准 SQL 字符串相同的答案。

## 两类检查解决不同问题

`execute_readonly` 负责“是否允许执行”，`validate_rows` 负责“执行结果是否符合本次口径”。第一类是操作约束，第二类是结果正确性验证，不能互相替代。

执行层包含：最多 20,000 字符且只能一条 SELECT 或 WITH SELECT；SQLite URI `mode=ro` 加 `query_only`；authorizer 仅允许白名单表读取和白名单函数；每 1000 个虚拟机操作检查预算，超过约 3 秒或 200 万步则中断；最多返回 100 行，列名不得重复。

这里不是完整的进程隔离沙箱。进度回调的时间预算不是严格实时硬截止，也不提供进程级内存限制。`sqlparse` 的用途是语句划分和类型检查，不能描述成完整 SQL AST 语义证明。提示词要求参数化，但代码没有单独证明 SQL 必须实际引用每个命名参数。

结果层检查行数、精确列集合、期间渠道唯一性，以及三个数值列是否为有限非负整数。整数值浮点数可以归一化；布尔值、NaN、负数和小数金额不可以。最后对排序后的每一行与每一个字段做完全比较。

## 独立明细核验到底独立在哪里

`reference_rows` 使用简单 SELECT 读取订单字典、退款列表和渠道，再在 Python 循环里逐条判断。它不复用模型的聚合 SQL，也不通过另一条相似 JOIN 查询生成答案。

```python
for 每个期间:
    for 每个渠道:
        for 每笔订单:
            if 支付成功且支付时间在窗口内且地区渠道匹配:
                订单数 += 1
                实付 += 订单实付
        for 每次退款:
            原订单 = 订单字典[退款.order_id]
            if 退款成功且退款时间在窗口内且原订单地区渠道匹配:
                退款额 += 本次退款金额
```

例如模型误用创建时间，它可能遗漏“七月创建、八月支付”的订单；独立计算仍按支付时间计入，二者就会不一致。一单多退造成的连接扇出同样会使金额不一致。

独立实现可以降低同一查询错误同时影响两条路径的概率，却不能证明业务定义本身正确。若团队一开始就选错了收入口径，SQL 和 Python 都按错误规则实现，二者仍可能完全一致。应先确认指标契约，再用独立计算检查实现。

它也不是面向海量生产数据的通用验证器：当前 Python 循环读取全部相关原始表，适合可控模拟数据。数据规模增加后，应考虑限定快照、分区对账或经过验证的指标层，不能照搬全量扫描。

## 为什么核验失败不偷偷换标准答案

live 模式失败时最多修复一次候选 SQL。修复失败就输出失败证据。若悄悄切换为标准 SQL，却仍标成“模型 SQL 已通过”，用户会误判模型能力，也无法复盘真实错误。

reference 模式则明确说明采用标准 SQL，适合不用模型做基线验证。这两个模式服务不同目的：一个测试真实生成链路，一个提供稳定对照。

最后记住：一次查询在当前数据库得到正确结果，不等于已经证明它在未来所有数据分布中语义等价。因此测试还要专门覆盖跨月、重复退款、零值渠道等反例。